Oracle supports at most 1000 items in a SQL query’s IN clause. When more items are given, the exception ORA-01795 maximum number
of expressions in a list is 1000 is raised. Thus, IN clauses are not as scalable as joins.
Noncompliant code example
BEGIN
SELECT *
INTO result
FROM my_table
WHERE col1 IN (1, 2, 3, ..., 1001); -- Noncompliant - raises ORA-01795
END;
/
Compliant solution
BEGIN
SELECT my_table.*
INTO result
FROM my_table
JOIN new_in_table
WHERE my_table.col1 = new_in_table.value; -- Compliant
END;
/
